﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Support;
using System.Data;
using MySql.Data.MySqlClient;


namespace SQLdbMySqlLib 
{


    public class MySQL : SQL
    {


        MySqlConnection conn;

        public void connect(string host, string username, string password, string database)
        {
            string connString = " SERVER=" + host + ";DATABASE=" + database + ";UID=" + username + ";PASSWORD=" + password + ";";
            //string connString = " SERVER=localhost;DATABASE=teamplay;UID=stawiu;PASSWORD=dupa;";
            this.conn = new MySqlConnection(connString);
            try
            {
                conn.Open();
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Connect to MySQL db failed",e);
                throw ex;
            }
        }


        public void disconnect()
        {
            try
            {
                conn.Close();
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Disconnect failed", e);
                throw ex;
            }
        }

        public DataSet select(string queryString)
        {
            DataSet dtSet = new DataSet();
            MySqlCommand command = conn.CreateCommand();
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            command.CommandText = queryString;
            adapter.SelectCommand = command;
            adapter.Fill(dtSet);
            return dtSet;
        }

        public void insert(MySqlCommand cmd) 
        {
            cmd.Connection = conn;
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.InsertCommand = cmd;
            adapter.InsertCommand.ExecuteNonQuery();
            //cmd.ExecuteNonQuery();
        }


        public void insertDataObject(int type, int FileSize, byte[] rawData, string name, int album)
        {
            MySqlCommand cmd = new MySqlCommand();

            string SQL = "INSERT INTO pictures VALUES(NULL, @Album, @Type, @Name, @FileSize, @Data)";

            
            cmd.CommandText = SQL;
            cmd.Parameters.AddWithValue("@Album", album);
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Type", type);
            cmd.Parameters.AddWithValue("@FileSize", FileSize);
            cmd.Parameters.AddWithValue("@Data", rawData);

            insert(cmd);
        }

        public DataSet getSession(string sessionID)
        {
            try
            {
                return select("SELECT * FROM session WHERE SessionID='" + sessionID+"'");
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataSet getDataObject(int objectID)
        {
            try
            {
                return select("SELECT Type, Album, Name, FileSize, Data FROM pictures WHERE ID= " + objectID);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataSet getAlbumContent(int albumID)
        {
            try
            {
                return select("SELECT ID, Type, Name FROM pictures WHERE ALBUM=" + albumID);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void createUserTable()
        {
            try
            {
                string SQL = "CREATE TABLE pictures (`ID` INT NOT NULL AUTO_INCREMENT ,`Album` INT NOT NULL ,`Type` TINYINT NOT NULL ,`Name` VARCHAR( 35 ) NOT NULL ,`FileSize` BIGINT ,`Data` LONGBLOB , PRIMARY KEY ( `ID` ))"; //; INSERT INTO `pictures` VALUES (-1, -2, 0, 'root', NULL, NULL )";
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;
                insert(cmd);
                createRoot();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void insertSession(string sessionID, byte[] keyBytes)
        {
            try
            {
                string SQL = "INSERT INTO session VALUES(@sessionID, @keyBytes, '', '', '', '', '', NULL)";

                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;
                cmd.Parameters.AddWithValue("@sessionID", sessionID);
                cmd.Parameters.AddWithValue("@keyBytes", keyBytes);

                insert(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataSet getUser(string userName)
        {
            try
            {
                return select("SELECT * FROM users WHERE Name='" + userName+"'");
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void updateSession(string sessionID, string DBtype, string DBurl, string DBuser, string DBpassword, string DBname)
        {
            try
            {
                string SQL = "UPDATE session SET DBtype=@dbtype, DBurl=@dburl, DBuser=@dbuser, DBpassword=@dbpassword, DBname=@dbname WHERE SessionID='"+sessionID+"'";

                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;
                cmd.Parameters.AddWithValue("@dbtype", DBtype);
                cmd.Parameters.AddWithValue("@dburl", DBurl);
                cmd.Parameters.AddWithValue("@dbuser", DBuser);
                cmd.Parameters.AddWithValue("@dbpassword", DBpassword);
                cmd.Parameters.AddWithValue("@dbname", DBname);
                insert(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void updateTimestamp(string sessionID)
        {
            try
            {
                string SQL = "UPDATE session SET LastAction = NULL WHERE SessionID='"+sessionID+"'";
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;
                insert(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void removeExpiredSessions()
        {
            try
            {
                string SQL = "DELETE FROM session WHERE LastAction< NOW()-300";
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;
                insert(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void deleteAlbumContent(int albumID)
        {
            try
            {
                string SQL = "DELETE FROM pictures WHERE Album=@albumID";
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;
                cmd.Parameters.AddWithValue("@albumID", albumID);
                insert(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void deleteDataObject(int id)
        {
            try
            {
                string SQL = "DELETE FROM pictures WHERE ID=@id";
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;
                cmd.Parameters.AddWithValue("@id", id);
                insert(cmd);
            }
            catch (Exception e)
            {
                throw e;
            } 
        }

        public void updateLocation(int id, int album)
        {
            try
            {
                string SQL = "UPDATE pictures SET Album=@albumID WHERE ID=@id";
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;
                cmd.Parameters.AddWithValue("@albumID", album);
                cmd.Parameters.AddWithValue("@id", id);
                insert(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void updateName(int id, string name)
        {
            try
            {
                string SQL = "UPDATE pictures SET Name=@name WHERE ID=@id";
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@id", id);
                insert(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataSet checkPictureTableExistance(string database)
        {
            try
            {
                return select("SHOW TABLES FROM " + database + " WHERE Tables_in_" + database + " = 'pictures'");
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void createRoot()
        {
            try
            {
                string SQL = "INSERT INTO `pictures` VALUES (-1, -2, 0, 'root', NULL, NULL )";
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = SQL;

                insert(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        
    }
}
